package com.party.admin.biz.system;

import com.google.common.collect.Lists;
import com.party.common.utils.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.List;

/**
 * 系统处理
 *
 * @author yifeng
 * @date 2018/7/10 0010
 * @time 上午 10:34
 */
@Service
public class SystemBizService {

    @Value("${jdbc.url}")
    private String url;

    @Value("${jdbc.driver}")
    private String driver;

    @Value("${jdbc.username}")
    private String username;

    @Value("${jdbc.password}")
    private String password;

    @Value("${jdbc.dbName}")
    private String dbName;

    Logger logger = LoggerFactory.getLogger(getClass());

    public List<String> getAllTableName(String dbName) {
        Connection connection = null;
        Statement statement = null;
        List<String> tableNames = Lists.newArrayList();
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
            statement = connection.createStatement();
            String sql = "SELECT table_name FROM information_schema.TABLES WHERE table_schema = '" + dbName + "' AND table_type = 'base table'";
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                String tableName = rs.getString("table_name");
                tableNames.add(tableName);
            }
        } catch (Exception e) {
            logger.error("获取所有表名异常", e);
            return Lists.newArrayList();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tableNames;
    }

    public String updateCollate() {
        if (StringUtils.isEmpty(dbName)) {
            return "数据库名称不能为空";
        }
        List<String> tableNames = this.getAllTableName(dbName);
        if (tableNames.size() == 0) {
            return "没有取到表名";
        }
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
            statement = connection.createStatement();
            //关闭自动提交事务，手动控制事务
            connection.setAutoCommit(false);
            List<String> array = Lists.newArrayList();
            for (String tableName : tableNames) {
                String updateTableSql = String.format("ALTER TABLE %s CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin", tableName);
                String updateTableDefaultSql = String.format("ALTER TABLE %s DEFAULT CHARACTER SET utf8 COLLATE utf8_bin", tableName);
                if (tableName.indexOf("qrtz_") == -1) {
                    statement.addBatch(updateTableSql);
                    statement.addBatch(updateTableDefaultSql);
                    array.add(updateTableSql);
                    array.add(updateTableDefaultSql);
                }
            }
            logger.error("执行字符集SQL语句：{}", array.toString());
            statement.executeBatch();
            connection.commit();
        } catch (Exception e) {
            try {
                connection.rollback();
                logger.error("异常", e);
                return e.getMessage();
            } catch (SQLException e1) {
                logger.error("异常", e1);
                return e1.getMessage();
            }
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                logger.error("异常", e);
                return e.getMessage();
            }
            try {
                if (connection != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                logger.error("异常", e);
                return e.getMessage();
            }
        }
        return null;
    }
}
